/**
 * 
 */
package com.qunar.crawler.DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.qunar.crawler.bean.Go117;
import com.qunar.crawler.html.HtmlPossesser117Go;

/**
 * @author tao.zhang
 */
public class DBUtil {
    private static String TOURS = "go117_tours";
    private static String PICS = "go117_pics";
    private static final Logger logger = LoggerFactory.getLogger(HtmlPossesser117Go.class);

    /** --------返回插入的图片数----------- */
    public static int addTours2DB(List<String> list) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int affectRowCount = 0;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "INSERT IGNORE INTO " + TOURS + " (url) VALUES(?) ";
            pstmt = conn.prepareStatement(sql);

            long begin = System.currentTimeMillis();
            for (String b : list) {
                pstmt.setString(1, b);
                pstmt.addBatch();
            }

            int[] result = pstmt.executeBatch();

            long end = System.currentTimeMillis();
            affectRowCount = 0;
            for (int i : result) {
                affectRowCount += i;
            }
            logger.info("追加旅程旅行URL到数据库耗时:" + (end - begin) + "ms. 本次增加<<" + affectRowCount + ">>个URL！" + "时间："
                    + new SimpleDateFormat("yyyy-MM-dd:HH:mm:ss").format(new Date()));
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            JdbcUtils.release(conn, pstmt, rs);
        }

        return affectRowCount;
    }

    /** --------返回插入的图片数----------- */
    public static int addGo1172DB(List<Go117> list) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int affectRowCount = 0;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "INSERT IGNORE INTO " + PICS + " (pic_url,DATE,username,tour_title,pub_date) VALUES(?,?,?,?,NOW()) ";
            pstmt = conn.prepareStatement(sql);

            long begin = System.currentTimeMillis();
            for (Go117 b : list) {
                if (b.getImgUrl() == null)
                    continue;
                pstmt.setString(1, b.getImgUrl());
                pstmt.setString(2, b.getDate());
                pstmt.setString(3, b.getUsername());
                pstmt.setString(4, b.getTourTitle());
                pstmt.addBatch();
            }

            int[] result = pstmt.executeBatch();

            long end = System.currentTimeMillis();
            affectRowCount = 0;
            for (int i : result) {
                affectRowCount += i;
            }
            if (affectRowCount > 0)
                logger.info("\n追加117GO图片到数据库耗时:" + (end - begin) + "ms. 本次增加<<" + affectRowCount + ">>张新图片！" + "时间："
                        + new SimpleDateFormat("yyyy-MM-dd:HH:mm:ss").format(new Date()));
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            JdbcUtils.release(conn, pstmt, rs);
        }

        return affectRowCount;
    }

}
